package com.scalar.chiptrack.sales.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.packingslip.CustomerShippingLineItems;
import com.scalar.chiptrack.operations.process.FinishedGoods;
import com.scalar.chiptrack.sales.LineItem;
import com.scalar.chiptrack.sales.SOShippingLocation;
import com.scalar.chiptrack.users.dao.NotificationSetupDAO;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import org.apache.struts.util.LabelValueBean;

import java.sql.*;
import java.util.*;

/**
 * Created by Sudhakar.
 * Date: Dec 9, 2004
 * Time: 10:58:53 PM
 */
public class NewSalesOrderDAO implements Constants {

    /*default construtcor*/
    private NewSalesOrderDAO()
    {

    }
    public static final String COOL_STATUS = "Cancellation needs approval";
    public static int getCustomerID( DBTransaction dbTransaction, String salesOrderID ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int customerID = -1;


        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get("load.customerID"));
            pstmt.setString( 1, salesOrderID );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
                customerID = rset.getInt( "Customer_ID" );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive customer ID", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return customerID;
    }

    public static int getJitWindow( DBTransaction dbTransaction, int customerID ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int jitWindow = -1;


        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get("load.customerJITWindow") );
            pstmt.setInt( 1, customerID );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
                jitWindow = rset.getInt( "C_JIT_WINDOW" );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive jitWindow", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return jitWindow;
    }
   //Added by varma 12 Dec 2004
    public static HashMap loadManfacturingPartNumbersWithOdmLabelNumbers( int customerId, int odmId, int isSample ) throws ScalarException
    {
       // Iterator finishedGoodsList = FinishedGoodsDAO.loadAllFinishedGoods().iterator();
        Iterator finishedGoodsList = loadAllFinishedGoodsWithOdmPartNos ( customerId, odmId, isSample ).iterator ();
        HashMap mapCustPartNos = new HashMap ();
        FinishedGoods finishedGoods = null;
        while ( finishedGoodsList.hasNext () )
        {
            finishedGoods = ( FinishedGoods ) finishedGoodsList.next ();
            String mfgPartNo = finishedGoods.getManufacturingPartNo ();
            String labelNumber = finishedGoods.getLabelNumber ();
            mapCustPartNos.put ( mfgPartNo, labelNumber );
        }

        return mapCustPartNos;
    }

     public static ArrayList loadAllFinishedGoodsWithOdmPartNos( int customerId, int odmId, int isSample ) throws ScalarException
    {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         ArrayList productList = new ArrayList();

         try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.finishedgoods.loadAllFinishedGoodsWithOdmPartNos"));
            pstmt.setInt( 1 , customerId );
            pstmt.setInt( 2, odmId );
            pstmt.setInt( 3, isSample );
            rs = pstmt.executeQuery();


            FinishedGoods finishedGoods = null;
            while(rs.next()) {
             finishedGoods = new FinishedGoods(
                             rs.getInt("ProcessStep_ID"),
                             rs.getString("IST_Manufacturing_Part_Number"),
                             rs.getString("IST_Marketing_Part_Number"),
                             rs.getInt("Product_ID"),
                             rs.getString("P_Type"),
                             rs.getString("P_Comments"),
                     rs.getString("Customer_PartNumber"));
              finishedGoods.setLabelNumber(rs.getString("Label_Number") );

                 productList.add(finishedGoods);
            }
         }
         catch (Exception sqlex) {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
         }
         finally {
             if (rs != null) {
                 try { rs.close(); } catch (SQLException excp) {}
             }
             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException excp) {}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException excp) {}
             }
         }

         return productList;
     }
    public static ArrayList loadAllFinishedGoodsWithOdmPartNosWithOutObsolete( int customerId, int odmId, int isSample ) throws ScalarException
    {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         ArrayList productList = new ArrayList();

         try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "{CALL sp_products_list_with_odm_part_numbers_with_out_OBSOLETE( ?, ?, ? ) }" );
            //pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.finishedgoods.loadAllFinishedGoodsWithOdmPartNos"));
            pstmt.setInt( 1 , customerId );
            pstmt.setInt( 2, odmId );
            pstmt.setInt( 3, isSample );
            rs = pstmt.executeQuery();


            FinishedGoods finishedGoods = null;
            while(rs.next()) {
             finishedGoods = new FinishedGoods(
                             rs.getInt("ProcessStep_ID"),
                             rs.getString("IST_Manufacturing_Part_Number"),
                             rs.getString("IST_Marketing_Part_Number"),
                             rs.getInt("Product_ID"),
                             rs.getString("P_Type"),
                             rs.getString("P_Comments"),
                     rs.getString("Customer_PartNumber"));
              finishedGoods.setLabelNumber(rs.getString("Label_Number") );

                 productList.add(finishedGoods);
            }
         }
         catch (Exception sqlex) {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
         }
         finally {
             if (rs != null) {
                 try { rs.close(); } catch (SQLException excp) {}
             }
             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException excp) {}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException excp) {}
             }
         }

         return productList;
     }
    public static void  loadOdmLabelNumbersForLineItems ( DBTransaction dbTransaction, ArrayList lineItems, int customerId, int odmId )throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

      try
      {
          if (dbTransaction == null)
          {
             conn = DataSource.getConnection();
          }
          else
          {
             conn = dbTransaction.getConnection();
          }
          if( odmId > 0 )
          {
              pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.odm.loadOdmPartNumber" ) );
          }
          else
          {
             pstmt = conn.prepareStatement ( "SELECT Label_Number from Customer_PartNumbers WHERE Customer_ID = ?  and Product_ID = ? and Odm_id is null" );
          }

          for(int i=0;i<lineItems.size();i++)
          {
              LineItem lineItem = (LineItem) lineItems.get(i);
              if( odmId > 0 )
              {
                  pstmt.setInt(1, customerId);
                  pstmt.setInt(2, odmId);
                  pstmt.setInt(3, lineItem.getProductId());
              }
              else
              {
                  pstmt.setInt(1, customerId);
                  //pstmt.setInt(2, odmId);
                  pstmt.setInt(2, lineItem.getProductId());
              }
              rs = pstmt.executeQuery();
              if(rs.next())
              {
                  lineItem.setLabelNumber(rs.getString("Label_Number") );
              }
              else
              {
                  lineItem.setLabelNumber("");
              }

          }
          success = true;
      }
      catch ( Exception sqlex )
      {
          sqlex.printStackTrace ();
          throw new ScalarException ( "Could not get End User Part Numbers from database", sqlex );
      }
        finally
        {
            if( dbTransaction == null)
            {
                try
                {	if( conn != null )
                    {
                        if( success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }catch(SQLException ignore){}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }//finally


    }//method

    public static void loadLineItemsWithCustomerAndOdmPartNumbers( DBTransaction dbTransaction ,ArrayList lineItemList, int customerId, int odmId )throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

      try
      {
          if (dbTransaction == null)
          {
             conn = DataSource.getConnection();
          }
          else
          {
             conn = dbTransaction.getConnection();
          }

          pstmt = conn.prepareStatement ( "select Customer_PartNumber from Customer_PartNumbers where Customer_Id = ? and Product_id = ? and Odm_id is null");

          /*for(int i=0;i<lineItemList.size();i++)
          {
              LineItem lineItem = (LineItem) lineItemList.get(i);
              pstmt.setInt( 1, customerId );
              pstmt.setInt( 2, lineItem.getProductId() );
              rs = pstmt.executeQuery();
              if(rs.next())
              {
                  lineItem.setCustomerPartNo(rs.getString("Customer_PartNumber") );
              }
              else
              {
                  lineItem.setCustomerPartNo("");
              }

          }*/
          for(int i=0;i<lineItemList.size();i++)
          {
              LineItem lineItem = (LineItem) lineItemList.get(i);
              pstmt.setInt( 1, odmId );
              pstmt.setInt( 2,lineItem.getProductId() );
              rs = pstmt.executeQuery();
              if(rs.next())
              {
                  lineItem.setOdmPartNo(rs.getString("Customer_PartNumber") );
              }
              else
              {
                  lineItem.setOdmPartNo("");
              }

          }
          success = true;
      }
      catch ( Exception sqlex )
      {
          sqlex.printStackTrace ();
          throw new ScalarException ( "Could not get End User Part Numbers from database", sqlex );
      }
        finally
        {
            if( dbTransaction == null)
            {
                try
                {	if( conn != null )
                    {
                        if( success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }catch(SQLException ignore){}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }//finally

    }

  //till here Additions by varma on 12 dec 2004

    public static void loadCSLineItemsWithCustomerAndOdmPartNumbers( ArrayList lineItemList, int salesOrderId )throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int customerId  = -1;
        int odmId       = -1;

        try
        {


            odmId = getOdmId( salesOrderId );
            conn =  null;

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement ( "select Customer_PartNumber from Customer_PartNumbers where Customer_Id = ? and Product_id = ? and Odm_id is null");

            /*for(int i=0 ; i < lineItemList.size(); i++ )
            {
                LineItem lineItem = (LineItem) lineItemList.get(i);
                pstmt.setInt( 1, customerId );
                pstmt.setInt( 2, lineItem.getProductId() );
                rs = pstmt.executeQuery();
                if( rs.next() )
                {
                    lineItem.setCustomerPartNo (rs.getString("Customer_PartNumber") );
                }
                else
                {
                    lineItem.setCustomerPartNo("");
                }
            }
            */
            for( int i = 0 ; i < lineItemList.size() ; i++ )
            {
                CustomerShippingLineItems lineItem = ( CustomerShippingLineItems ) lineItemList.get(i);
                pstmt.setInt( 1, odmId );
                pstmt.setInt( 2, lineItem.getProduct_ID() );
                rs = pstmt.executeQuery();

                if( rs.next() )
                {
                    //lineItem.setOdmPartNo( rs.getString("Customer_PartNumber") );
                    lineItem.setOdm_Part_Number( rs.getString("Customer_PartNumber") );
                }
                else
                {
                    //lineItem.setOdmPartNo("");
                    lineItem.setOdm_Part_Number( "" );
                }
                //System.out.println("---------------------NewSalesOrderDAO-OdmPartNumber " + lineItem.getOdm_Part_Number() + " odmId = " + odmId + " Product_ID = " + lineItem.getProduct_ID() + " salesOrderId = " + salesOrderId);
            }
            success = true;
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get End User Part Numbers from database", sqlex );
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }
        }//finally
    }

    public static int getOdmId ( int salesOrderID ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int odmId = -1;


        try
        {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( "SELECT SO_ODM_ID From Sales_Order Where SalesOrder_ID = ?" );
            pstmt.setInt( 1, salesOrderID );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                odmId = rset.getInt( "SO_ODM_ID" );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive customer ID", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return odmId;
    }

    public static ArrayList loadShippingInstructions( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList shippingInstructionsList = new ArrayList();
        String shippingInstructions = null;
        boolean success = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.loadShippingInstructions" ) );
            rs = pstmt.executeQuery();
            shippingInstructionsList.add( new LabelValueBean( "------ Select ------", "-1" ) );
            while( rs.next() )
            {
                shippingInstructions = rs.getString( "SHIPPING_INSTRUCTIONS" );
                shippingInstructions = shippingInstructions.length() < 29 ? shippingInstructions:shippingInstructions.substring( 0, 25 ) + "...";
                shippingInstructionsList.add( new LabelValueBean( shippingInstructions, rs.getInt( "SI_ID" ) + "" ) );
            }
            shippingInstructionsList.add( new LabelValueBean( "-- Add New Shipping Instructions --", "-2" ) );
            success = true;

        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return shippingInstructionsList;
    }

    public static String loadSelectedShippingInstructions( DBTransaction dbTransaction, int shippingInstructionId ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String shippingInstructions = null;
        boolean success = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.loadSelectedShippingInstructions" ) );
            pstmt.setInt( 1, shippingInstructionId );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                shippingInstructions = rs.getString( "SHIPPING_INSTRUCTIONS" );
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return shippingInstructions;
    }

    public static int saveShippingInstructions( DBTransaction dbTransaction, String shippingInstructions ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int shippingInstructionsId = -1;
        boolean success = false;

        try
        {
            if ( dbTransaction == null  ) {
                conn = DataSource.getConnection();
            } else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.salesOrder.saveShippingInstructions") );
            pstmt.setString( 1, shippingInstructions);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement( "SELECT @@IDENTITY AS SI_ID" );
            rs = pstmt.executeQuery();
            while( rs.next() ) {
                shippingInstructionsId = rs.getInt("SI_ID");
            }

            success = true;
        } catch ( Exception e ) {
            e.printStackTrace();
        }
        finally {
            if (dbTransaction == null) {
                 try {
                     if (conn != null) {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return shippingInstructionsId;
    }

    public static ArrayList loadFreightInstructions( DBTransaction dbTransaction ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList freightInstructionsList = new ArrayList();
        String freightInstructions = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null  )
            {
                conn = DataSource.getConnection();
            } else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.salesOrder.loadFreightInstructions") );
            rs = pstmt.executeQuery();
            freightInstructionsList.add( new org.apache.struts.util.LabelValueBean( "-----Select-----","-1") );
            while( rs.next() )
            {
                freightInstructions = rs.getString("Freight_Instructions");
                freightInstructions = freightInstructions.length() < 29 ? freightInstructions : freightInstructions.substring(0,25)+"...";
                freightInstructionsList.add( new org.apache.struts.util.LabelValueBean( freightInstructions, rs.getInt("FI_ID")+"" ) );
            }
            freightInstructionsList.add( new org.apache.struts.util.LabelValueBean( "--Add New Freight Instructions--","-2") );
            success = true;

        }
        catch ( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
            if ( rs != null )
            {
                try { rs.close(); } catch ( SQLException ignore ) { }
            }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return freightInstructionsList;
    }
    public static int saveFreightInstructions( DBTransaction dbTransaction, String freightInstructions ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int freightInstructionsId = -1;
        boolean success = false;

        try
        {
            if ( dbTransaction == null  )
            {
                conn = DataSource.getConnection();
            } else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.salesOrder.saveFreightInstructions") );
            pstmt.setString( 1, freightInstructions);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement( "SELECT @@IDENTITY AS FI_ID" );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                freightInstructionsId = rs.getInt("FI_ID");
            }

            success = true;
        } catch ( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
            if ( rs != null )
            {
                try { rs.close(); } catch ( SQLException ignore ) { }
            }
             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return freightInstructionsId;
    }
    public static String loadSelectedFreightInstructions( DBTransaction dbTransaction, int freightInstructionId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String freightInstructions = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null  )
            {
                conn = DataSource.getConnection();
            } else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.salesOrder.loadSelectedFreightInstructions") );
            pstmt.setInt( 1, freightInstructionId);
            rs = pstmt.executeQuery();
            if ( rs.next() )
            {
                freightInstructions = rs.getString("Freight_Instructions");
            }
            success = true;
        } catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could Not load the selected Freight Instructions" );
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return freightInstructions;
    }
   //to load the MFG Part Numbers with Customer Part Numbers. this method loads part numbers map that specific to client
   //If client is Airgo need to show only customer part numbers either sales order is generated for customer and ODM or for customer
   //If Client is Cavium need to show the customer part numbers if sales order is for customer or if sales order is to Customer-End User then load End User Part Numbers
   public static HashMap loadManfacturingPartNumbersWithCustomerPartNumbers( int customerId, int odmId, int isSample ) throws ScalarException
   {
       Iterator finishedGoodsList = null ;
       if( ConfigManager.isEndUserPartNumbersAllowed() )
       {
           //finishedGoodsList = NewSalesOrderDAO.loadAllFinishedGoodsWithOdmPartNos(customerId, odmId, isSample ).iterator();
       }
       else
       {
           odmId = 0;
           //finishedGoodsList = NewSalesOrderDAO.loadAllFinishedGoodsWithOdmPartNos(customerId, 0, isSample ).iterator();
       }

       finishedGoodsList = NewSalesOrderDAO.loadAllFinishedGoodsWithOdmPartNos(customerId, odmId, isSample ).iterator();
       HashMap mapCustPartNos = new HashMap();
       FinishedGoods finishedGoods = null;
       while(finishedGoodsList.hasNext())
       {
             finishedGoods = (FinishedGoods) finishedGoodsList.next();
             String mfgPartNo = finishedGoods.getManufacturingPartNo();
           mapCustPartNos.put( mfgPartNo, loadCustomerPartNumbers( null, finishedGoods.getProductId(), customerId, odmId) );
        }

       return mapCustPartNos;
   }
   //to load the Customer part numbers defferentiating the client Names
    public static void loadCustomerPartNumbersForCSLineItems ( DBTransaction dbTransaction, ArrayList lineItems, int customerId, int odmId )throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

      try
      {
          if (dbTransaction == null)
          {
             conn = DataSource.getConnection();
          }
          else
          {
             conn = dbTransaction.getConnection();
          }
          //pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.loadCustomerPartNumber" ) );

          //If Client is Cavium then load the part numbers of customer if sales order is generated for customer.load ODM(End Customer)
          //part numbers if sasles order is generated for customer and ODM(End Customer) combination.
          if( ConfigManager.isEndUserPartNumbersAllowed() )
          {
              if( odmId > 0 ){
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.loadEndCustomerPartNumber" ) );
                pstmt.setInt( 1, customerId );
                pstmt.setInt( 2, odmId );
              }else{
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.loadCustomerPartNumber" ) );
                pstmt.setInt( 1, customerId );
              }

              for(int i=0;i<lineItems.size();i++)
              {
                  CustomerShippingLineItems lineItem = (CustomerShippingLineItems) lineItems.get(i);
                  if( odmId > 0 ){
                      pstmt.setInt( 3, lineItem.getProduct_ID() );
                  }else{
                      pstmt.setInt( 2, lineItem.getProduct_ID() );
                  }
                  rs = pstmt.executeQuery();
                  if(rs.next())
                  {
                      lineItem.setCustomer_Part_Number(rs.getString("Customer_PartNumber"));
                  }
                  else
                  {
                      lineItem.setCustomer_Part_Number("");
                  }
              }
          }
          //If the client is Airgo then load part numbers of customer whether sales order is generated for customer or ODM
          else
          {
              pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.loadCustomerPartNumber" ) );
              for(int i=0;i<lineItems.size();i++)
              {
                  CustomerShippingLineItems lineItem = (CustomerShippingLineItems) lineItems.get(i);
                  pstmt.setInt(1, customerId);
                  pstmt.setInt(2, lineItem.getProduct_ID() );
                  rs = pstmt.executeQuery();
                  if(rs.next())
                  {
                      lineItem.setCustomer_Part_Number(rs.getString("Customer_PartNumber")) ;
                  }
                  else
                  {
                      lineItem.setCustomer_Part_Number("");
                  }
              }

          }
          success = true;
      }
      catch ( Exception sqlex )
      {
          sqlex.printStackTrace ();
          throw new ScalarException ( "Could not get Customer Part Numbers from database", sqlex );
      }
        finally
        {
            if( dbTransaction == null)
            {
                try
                {	if( conn != null )
                    {
                        if( success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }catch(SQLException ignore){}
            }
            if( rs != null )
            {
                try{ rs.close(); }catch(SQLException ignore){}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }//finally

    }//method

    //To load the Customer Part Numbers based on the client names.by getting the customer id and Odm id from the Line Items list.
    public static void loadCustomerPartNumbersForSearch ( DBTransaction dbTransaction, ArrayList lineItems )throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt1 = null;
        ResultSet rs = null;
        boolean success = false;

      try
      {
          if (dbTransaction == null)
          {
             conn = DataSource.getConnection();
          }
          else
          {
             conn = dbTransaction.getConnection();
          }
          pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.loadCustomerPartNumber" ) );
          pstmt1 = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.loadEndCustomerPartNumber" ) );
          //If Client is Cavium then load the part numbers of customer if sales order is generated for customer.load ODM(End Customer)
          //part numbers if sasles order is generated for customer and ODM(End Customer) combination.
          if( ConfigManager.isEndUserPartNumbersAllowed() )
          {
              for(int i=0;i<lineItems.size();i++)
              {
                  LineItem lineItem = (LineItem) lineItems.get(i);
                  int odmId =  lineItem.getOdmId() ;
                  int customerId = lineItem.getCustomerId() ;
                  if( odmId > 0 ){
                      pstmt1.setInt( 1, customerId );
                      pstmt1.setInt( 2, odmId );
                      pstmt1.setInt( 3, lineItem.getProductId() );
                      rs = pstmt1.executeQuery();
                  }else{
                      pstmt.setInt( 1, customerId );
                      pstmt.setInt( 2, lineItem.getProductId() );
                      rs = pstmt.executeQuery();
                  }
                  //rs = pstmt.executeQuery();
                  if(rs.next())
                  {
                      lineItem.setCustomerPartNo(rs.getString("Customer_PartNumber")) ;
                  }
                  else
                  {
                      lineItem.setCustomerPartNo("");
                  }
              }
          }
          //If the client is Airgo then load part numbers of customer whether sales order is generated for customer or ODM
          else
          {
              for(int i=0;i<lineItems.size();i++)
              {
                  LineItem lineItem = (LineItem) lineItems.get(i);
                  int customerId = lineItem.getCustomerId() ;
                  pstmt.setInt(1, customerId);
                  pstmt.setInt(2, lineItem.getProductId() );
                  rs = pstmt.executeQuery();
                  if(rs.next())
                  {
                      lineItem.setCustomerPartNo(rs.getString("Customer_PartNumber")) ;
                  }
                  else
                  {
                      lineItem.setCustomerPartNo("");
                  }
              }

          }
          success = true;
      }
      catch ( Exception sqlex )
      {
          sqlex.printStackTrace ();
          throw new ScalarException ( "Could not get Customer Part Numbers from database", sqlex );
      }
        finally
        {
            if( dbTransaction == null)
            {
                try
                {	if( conn != null )
                    {
                        if( success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }catch(SQLException ignore){}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( pstmt1 != null )
            {
                try{ pstmt1.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }//finally

    }//method

   public static ArrayList loadEndUsersWithEndUserIds() throws ScalarException
   {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    ArrayList endUsersList = new ArrayList();

        try
        {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT DISTINCT C.C_NAME AS SO_EndUser, SO_ODM_ID  FROM SALES_ORDER SO, CUSTOMER C WHERE SO_ODM_ID = C.CUSTOMER_ID ORDER BY SO_EndUser" );

            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                //endUsersList.add(rs.getString("SO_EndUser"));
                endUsersList.add( new org.apache.struts.util.LabelValueBean( rs.getString("SO_EndUser") , rs.getInt("SO_ODM_ID")+ "" ) );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Regions from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }
        return endUsersList;
   }

   /*
   *  Method to get all the SalesOrder Nos( with cama seperated ) for the given customerId and customerPo No
   */
   public static String getSalesOrderNoForCustomerPO( DBTransaction dbTransaction, String customerPoNo, int customerID, String salesOrderNo ) throws ScalarException {

        Connection        conn             = null;
        PreparedStatement pstmt            = null;
        ResultSet         rs               = null;
        StringBuffer      oldSalesOrderNos = new StringBuffer();
        boolean           success          = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            } else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.salesOrder.getSalesOrderNoForCustomerPO") );
            pstmt.setString( 1, customerPoNo );
            pstmt.setInt( 2, customerID );
            pstmt.setString( 3, salesOrderNo );
            rs = pstmt.executeQuery();
            while( rs.next() ) {
                oldSalesOrderNos.append( oldSalesOrderNos.length() > 0 ? ", "+rs.getString("SALESORDER_NO") : rs.getString("SALESORDER_NO") );
            }
            success = true;
        } catch ( Exception sqlex ) {
            sqlex.printStackTrace();
            throw new ScalarException("Could not load the SalesOrder No for the CustomerPO No");
        }
        finally {
            if( dbTransaction == null)
            {
                try
                {	if( conn != null )
                    {
                        if( success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }catch(SQLException ignore){}
            }
            if ( rs != null )
            {
                try { rs.close(); } catch ( SQLException excp ) {}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); } catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); } catch(SQLException ignore){}
            }
        }
        return oldSalesOrderNos.toString();
   }

    // Added by Veeru on 29th May 2005
    public static String[] loadEmailListForSalesOrderPorting()
    {
        String[] emailArray = null;
        try
        {
            emailArray = NotificationSetupDAO.loadEmailsByType( NOTIFICATION_EMAIL_TYPE_SALES_ORDER_PORTING );
        }
        catch( ScalarException se )
        {
            se.getRootCause().printStackTrace();
            System.out.println( "Exception while retrieving email list");
        }

        return emailArray;
    }

    public static ArrayList loadSalesPersons() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList salesPersonsList = new ArrayList ();

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( "select distinct salesPerson_Id,salesPerson_Name from sales_persons where deactivated = 0 order by salesPerson_Name" );

            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                String spId = rs.getString ( "salesPerson_Id" );
                String spName = rs.getString ( "salesPerson_Name" );
                salesPersonsList.add ( new org.apache.struts.util.LabelValueBean ( spName, spId ) );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Sales Persons from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return salesPersonsList;
    }

    /**
     *
     * @param addressesList
     * @param customerId
     * @param addressType "Shipping" or "Billing"
     * @throws ScalarException
     */
    public static void loadCustomerAddressesOfType(ArrayList addressesList, int customerId, String addressType) throws ScalarException {
        int defaultAddrsId;
        if (Constants.ADDRESS_TYPE_SHIPPING.equalsIgnoreCase(addressType)) {
            defaultAddrsId = SalesOrderDAO.getDefaultShippingAddressId(customerId);
        } else {
            defaultAddrsId = SalesOrderDAO.getDefaultBillingAddressId(customerId);
        }

        Address defAddress = CustomerDAO.getAddress(defaultAddrsId);
        if (defAddress != null) {
            String addrStr = "";
            if ((defAddress.getAddress1() != null) && (defAddress.getAddress1().trim().length() != 0)) {
                addressesList.add(new LabelValueBean(defAddress.getAddress1(), Integer.toString(defAddress.getAddressId())));
            }
            if ((defAddress.getAddress2() != null) && (defAddress.getAddress2().trim().length() != 0)) {
                addressesList.add(new LabelValueBean(defAddress.getAddress2(), Integer.toString(defAddress.getAddressId())));
            }
            if ((defAddress.getAddress3() != null) && (defAddress.getAddress3().trim().length() != 0)) {
                addressesList.add(new LabelValueBean(defAddress.getAddress3(), Integer.toString(defAddress.getAddressId())));
            }
            if ((defAddress.getAddress4() != null) && (defAddress.getAddress4().trim().length() != 0)) {
                addressesList.add(new LabelValueBean(defAddress.getAddress4(), Integer.toString(defAddress.getAddressId())));
            }
            if ((defAddress.getCity() != null) && (defAddress.getCity().trim().length() != 0)) {
                addressesList.add(new LabelValueBean(defAddress.getCity(), Integer.toString(defAddress.getAddressId())));
            }
            if ((defAddress.getState() != null) && (defAddress.getState().trim().length() != 0)) {
                addrStr = addrStr + defAddress.getState();
            }
            if ((defAddress.getZipCode() != null) && (defAddress.getZipCode().trim().length() != 0)) {
                if ((defAddress.getState() != null) && (defAddress.getState().trim().length() != 0)) {
                    addrStr = addrStr + "-";
                }
                addrStr = addrStr + defAddress.getZipCode();
            }
            if (((defAddress.getCountry() != null) && (defAddress.getCountry().trim().length() != 0)) || ((defAddress.getZipCode() != null) && (defAddress.getZipCode().trim().length() != 0))) {
                if ((defAddress.getState() != null) && (defAddress.getState().trim().length() != 0)) {
                    addrStr = addrStr + ",";
                }
                addrStr = addrStr + defAddress.getCountry();
            }
            if (!("".equals(addrStr))) {
                addressesList.add(new LabelValueBean(addrStr, Integer.toString(defAddress.getAddressId())));
            }
            addressesList.add(new LabelValueBean("          ", Integer.toString(defAddress.getAddressId())));
        }

        ArrayList addresses = CustomerDAO.getAllAddressesOfType(customerId, addressType);
        for (int i = 0; i < addresses.size(); i++) {
            Address address = (Address) addresses.get(i);
            String addrStr = "";
            if ((address.getAddress1() != null) && (address.getAddress1().trim().length() != 0)) {
                addressesList.add(new LabelValueBean(
                        address.getAddress1(), Integer.toString(address.getAddressId())));
            }
            if ((address.getAddress2() != null) && (address.getAddress2().trim().length() != 0)) {
                addressesList.add(new LabelValueBean(
                        address.getAddress2(), Integer.toString(address.getAddressId())));
            }
            if ((address.getAddress3() != null) && (address.getAddress3().trim().length() != 0)) {
                addressesList.add(new LabelValueBean(
                        address.getAddress3(), Integer.toString(address.getAddressId())));
            }
            if ((address.getAddress4() != null) && (address.getAddress4().trim().length() != 0)) {
                addressesList.add(new LabelValueBean(
                        address.getAddress4(), Integer.toString(address.getAddressId())));
            }
            if ((address.getCity() != null) && (address.getCity().trim().length() != 0)) {
                addressesList.add(new LabelValueBean(
                        address.getCity(), Integer.toString(address.getAddressId())));
            }
            if ((address.getState() != null) && (address.getState().trim().length() != 0)) {
                addrStr = addrStr + address.getState();
            }
            if ((address.getZipCode() != null) && (address.getZipCode().trim().length() != 0)) {
                if ((address.getState() != null) && (address.getState().trim().length() != 0)) {
                    addrStr = addrStr + "-";
                }
                addrStr = addrStr + address.getZipCode();
            }
            if ((address.getCountry() != null) && (address.getCountry().trim().length() != 0)) {
                if (((address.getZipCode() != null) && (address.getZipCode().trim().length() != 0)) || ((address.getState() != null) && (address.getState().trim().length() != 0))) {
                    addrStr = addrStr + ",";
                }
                addrStr = addrStr + address.getCountry();
            }
            if (!("".equals(addrStr))) {
                addressesList.add(new LabelValueBean(addrStr, Integer.toString(address.getAddressId())));
            }
            addressesList.add(new LabelValueBean("", Integer.toString(address.getAddressId())));
        }
    }
    public static boolean isAddressIdExists ( ArrayList shippingAddressesList, int addressId )
    {
        boolean isExists = false;
        int listSize = shippingAddressesList == null ? 0 : shippingAddressesList.size();
        int listAddrsId = -1;
        for ( int i=0; i < listSize; i++ )
        {
            LabelValueBean labelValueBean = (LabelValueBean)shippingAddressesList.get( i );
            try
            {
                listAddrsId = Integer.parseInt( labelValueBean.getValue() );
            }
            catch ( NumberFormatException e )
            {
                listAddrsId = -1;
            }
            if ( addressId == listAddrsId )
            {
                isExists = true;
                break;
            }
        }
        return isExists;
    }

    /**
     * This method will return the auto generated sales order number to be used to create new sales order. <br>
     * Auto Generated Sales Order Number will be in the format: <b>&lt;SO PREFIX&gt;-&lt;yMMdd&gt;&lt;seq&gt; </b><br>
     * where <b>&lt;SO PREFIX&gt;</b> - sales Order Prefix which is set in the company setup. if it is empty then hyphen(-) is not added to the auto sales order number.<br>
     * <b>&lt;yMMdd&gt;</b> - single digit year(y) Two digit month(MM) Two digit day(dd). month range will be 01-12 and day will be 01-31<br>
     * <b>&lt;seq&gt;</b> - sequence number. range will be 00 - 99<br>
     * For Ex: SO-7011700 --> SO - Prefix <br>
     *                    --> 7 - year(2007)<br>
     *                    --> 01 - month(January)<br>
     *                    --> 17 - day<br>
     *                    --> 00 - sequence</br>
     * when SO prefix is not given then the auto SO number will be: <b>7011700</b> 
     * @return returns the auto generated sales order number.
     */
    public static String generateAutomatedSalesOrderNo() throws ScalarException
    {
        String newSalesOrderNumber = null;
        String sequence = "00";
        String salesOrderNumber = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        String salesOrderPrefix = null;
        int salesOrderLength = 0;
        Calendar cl	= Calendar.getInstance();
        newSalesOrderNumber = StringUtils.formatDateToString(cl.getTime(), "yyMMdd" );
        newSalesOrderNumber = newSalesOrderNumber.substring(1);

        try
        {
            conn = DataSource.getConnection();
            /*pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getSalesOrderInfo" ) );
            rset = pstmt.executeQuery();
            if( rset.next())
            {
                salesOrderPrefix = rset.getString("Sales_Order_Prefix");
                salesOrderLength = rset.getInt("Sales_Order_Length");
            }

            try { rset.close(); rset = null; } catch ( SQLException ignore ) {}
            try { pstmt.close(); pstmt = null; } catch ( SQLException ignore ) {}*/

            pstmt = conn.prepareStatement( "SELECT  TOP 1 SALESORDER_NO FROM sales_order WHERE LEN(SUBSTRING(SalesORder_NO,CHARINDEX('-',SalesORder_NO)+1,LEN(SalesORder_NO))) = 7 AND SO_IS_SAMPLE= 1 ORDER BY SALESORDER_ID DESC" );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                salesOrderNumber = rset.getString ( "SALESORDER_NO" );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not get Automated Sales Order No from database", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        if ( salesOrderNumber != null )
        {
            int seq = 0;
            try
            {
                seq = Integer.parseInt ( salesOrderNumber.substring( salesOrderNumber.length()-2 ) );
            } catch ( NumberFormatException e ) { seq = -1; }
            seq = seq + 1;
            if ( seq > 99 )
            {
                seq = 0;
            }
            sequence = (seq<10) ? "0"+seq : Integer.toString(seq);
        }
        newSalesOrderNumber = newSalesOrderNumber + sequence;
        /*if( salesOrderPrefix != null && salesOrderPrefix.trim().length() != 0 )
        {
            newSalesOrderNumber = salesOrderPrefix + "-" + newSalesOrderNumber;
        }*/
        return newSalesOrderNumber;
    }
    public static String generateAutomatedSampleSalesOrderNo() throws ScalarException
    {
        String newSalesOrderNumber = null;
        String sequence = "99";
        String salesOrderNumber = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        String salesOrderPrefix = null;
        int salesOrderLength = 0;
        Calendar cl	= Calendar.getInstance();
        newSalesOrderNumber = StringUtils.formatDateToString(cl.getTime(), "yyMMdd" );
        newSalesOrderNumber = newSalesOrderNumber.substring(1);

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT  TOP 1 SALESORDER_NO FROM sales_order WHERE LEN(SUBSTRING(SalesORder_NO,CHARINDEX('-',SalesORder_NO)+1,LEN(SalesORder_NO))) = 8 AND SO_IS_SAMPLE=0 ORDER BY SALESORDER_ID DESC" );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                salesOrderNumber = rset.getString ( "SALESORDER_NO" );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not get Automated Sales Order No from database", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }

        if ( salesOrderNumber != null )
        {
            int seq = 99;
            try
            {
                seq = Integer.parseInt ( salesOrderNumber.substring( salesOrderNumber.length()-2 ) );
            }
            catch ( NumberFormatException e ) { seq = -1; }
            seq = seq - 1;
            if ( seq < 1 )
            {
                seq = 99;
            }
            
            sequence = (seq<10) ? "0"+seq : Integer.toString(seq);
        }

        newSalesOrderNumber = "S" + newSalesOrderNumber + sequence;
        return newSalesOrderNumber;
    }
    /**
     * This method returns the customer part numbers for the given Mfg Part number, Customer and odm
     *
     * @param dbTransaction
     * @param productID
     * @param customerID
     * @param odmID
     * @return CustomerPartNumbersList
     * @throws ScalarException
     */
    public static ArrayList loadCustomerPartNumbers( DBTransaction dbTransaction, int productID, int customerID, int odmID ) throws ScalarException
    {
        Connection        conn  = null;
        PreparedStatement pstmt = null;
        ResultSet         rset  = null;

        String CustomerPartNumber = null;
        ArrayList CustomerPartNumbersList = new ArrayList();
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            if ( odmID > 0 )
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.customer.loadCustomerPartNumbers"));
                pstmt.setInt( 1, productID );
                pstmt.setInt( 2, customerID );
                pstmt.setInt( 3, odmID );
            }
            else
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get("sales.customer.loadEndCustomerPartNumbers"));
                pstmt.setInt( 1, productID );
                pstmt.setInt( 2, customerID );
            }
            rset = pstmt.executeQuery();
            while( rset.next() )
            {
                CustomerPartNumber = rset.getString( "Customer_PartNumber" );
                CustomerPartNumbersList.add( CustomerPartNumber );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Exception in loadCustomerPartNumbers()... Could not load Customer PartNumbers", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return CustomerPartNumbersList;
    }
    /**
     * This method is to load the Customer Part number for the Quoattion Line Item respective of Customer or ODM
     * @param dbTransaction
     * @param lineItems
     * @param customerId
     * @param odmId
     * @throws ScalarException
     **/
    public static void loadCustomerPartNumbersForQuoteLineItems( DBTransaction dbTransaction, ArrayList lineItems, int customerId, int odmId )throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

      try
      {
          if (dbTransaction == null)
          {
             conn = DataSource.getConnection();
          }
          else
          {
             conn = dbTransaction.getConnection();
          }

          if( odmId > 0 )
          {
            pstmt = conn.prepareStatement( "SELECT Customer_PartNumber FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = ?  AND ODM_ID = ? AND PRODUCT_ID = ? AND CUSTOMER_PARTNUMBER IN ( SELECT ISNULL(QLI_CUSTOMER_PARTNUMBER, '') FROM QUOTATION_ORDER_LINEITEM WHERE LINEITEM_ID = ? ) ");
            pstmt.setInt( 1, customerId );
            pstmt.setInt( 2, odmId );
          }
          else
          {
            pstmt = conn.prepareStatement( "SELECT Customer_PartNumber FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = ? AND PRODUCT_ID = ? AND ODM_ID IS NULL AND CUSTOMER_PARTNUMBER IN ( SELECT ISNULL(QLI_CUSTOMER_PARTNUMBER, '') FROM QUOTATION_ORDER_LINEITEM WHERE LINEITEM_ID = ? ) ");
            pstmt.setInt( 1, customerId );
          }
          for(int i=0;i<lineItems.size();i++)
          {
              LineItem lineItem = (LineItem) lineItems.get(i);
              if( odmId > 0 ){
                  pstmt.setInt( 3, lineItem.getProductId() );
                  pstmt.setInt( 4, lineItem.getLineItemId() );
              }else{
                  pstmt.setInt( 2, lineItem.getProductId() );
                  pstmt.setInt( 3, lineItem.getLineItemId() );
              }

              rs = pstmt.executeQuery();
              if(rs.next())
              {
                  lineItem.setCustomerPartNo(rs.getString("Customer_PartNumber"));
              }
              else
              {
                  lineItem.setCustomerPartNo("");
              }
          }
          success = true;
      }
      catch ( Exception sqlex )
      {
          sqlex.printStackTrace ();
          throw new ScalarException ( "Could not get Customer Part Numbers from database", sqlex );
      }
        finally
        {
            if( dbTransaction == null)
            {
                try
                {	if( conn != null )
                    {
                        if( success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }catch(SQLException ignore){}
            }
            if( rs != null )
            {
                try{ rs.close(); }catch(SQLException ignore){}
            }
            if( pstmt != null )
            {
                try{ pstmt.close(); }catch(SQLException ignore){}
            }
            if( conn != null )
            {
                try{ conn.close(); }catch(SQLException ignore){}
            }

        }//finally

    }//method

    public static ArrayList loadShippedSalesOrderNumbersByCustomerId ( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList salesOrdersList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            if ( customerId <= 0 )
            {
                pstmt = conn.prepareStatement ( "SELECT SO.SALESORDER_NO, SO.SALESORDER_ID FROM SALES_ORDER SO, CUSTOMER_SHIPPING_TABLE CST WHERE CST.SALESORDER_ID = SO.SALESORDER_ID AND CST.CST_STATUS = 'SHIPPED' GROUP BY SO.SALESORDER_NO, SO.SALESORDER_ID ORDER BY SO.SALESORDER_NO DESC" );
            }
            else
            {
                pstmt = conn.prepareStatement( "SELECT SO.SALESORDER_ID, SO.SALESORDER_NO FROM SALES_ORDER SO, CUSTOMER_SHIPPING_TABLE CST WHERE CST.SALESORDER_ID = SO.SALESORDER_ID AND CST.CST_STATUS = 'SHIPPED' AND SO.CUSTOMER_ID = ? GROUP BY SO.SALESORDER_NO, SO.SALESORDER_ID ORDER BY SO.SALESORDER_NO DESC" );
                pstmt.setInt ( 1, customerId );
            }
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                salesOrdersList.add ( new LabelValueBean( rset.getString ( "SALESORDER_NO" ), rset.getString ( "SALESORDER_ID" ) ) );
            }            
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load shipped sales order nos", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return salesOrdersList;
    }

    public static String[] loadCustomerNotificationsEmails(int customerId, String salesOrderOrQuote) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String emails = "";

        try
        {

            conn = DataSource.getConnection();

            if( "Quotation".equalsIgnoreCase( salesOrderOrQuote ) )
            {
                pstmt = conn.prepareStatement( "SELECT Email FROM fn_load_customer_contact_person_quote_ack_emails( ? ) " );
            }
            else
            {
                pstmt = conn.prepareStatement( "SELECT Email FROM fn_load_customer_recieve_notification_emails( ? ) " );
            }

            pstmt.setInt( 1, customerId );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                emails = emails + rset.getString( "Email" ) + ",";
            }

            if( emails != null && emails.length() > 0 )
            {
                emails =  emails.substring( 0 , emails.length() - 1 );
            }
            emails = StringUtils.removeDuplicateEmailIds(emails);
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Customer Notification Email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

        if( ( emailArray != null ) && ( emailArray.length > 0 ) )
        {
            Arrays.sort( emailArray );
        }

        return emailArray;
    }

    public static ArrayList loadSalesOrdersForShipLocatoins ( DBTransaction dbTransaction, int productId, int customerId, String salesOrderNo, int scheduledIn, boolean showOnlyNoShipLocationSOs, int isSample ) throws ScalarException
    {
        ArrayList salesOrders = new ArrayList();
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall( "{CALL SP_LOAD_SALES_ORDERS_FOR_SHIPPING_LOCATIONS(?, ?, ?, ?, ?, ?)}");
            cstmt.setInt ( 1, productId );
            cstmt.setInt ( 2, customerId );
            cstmt.setString ( 3, salesOrderNo );
            cstmt.setInt ( 4, scheduledIn );
            cstmt.setInt ( 5, showOnlyNoShipLocationSOs ? 0 : 1 );
            cstmt.setInt ( 6, isSample );

            rset = cstmt.executeQuery();
            while ( rset.next () )
            {
                SOShippingLocation soShippingLocation = new SOShippingLocation();
                soShippingLocation.setSalesOrderId( rset.getInt ( "SALESORDER_ID" ) );
                soShippingLocation.setSalesOrderNo( rset.getString ( "SALESORDER_NO" ) );
                soShippingLocation.setLineItemId( rset.getInt ( "LINEITEM_ID" ) );
                soShippingLocation.setLineItemNo( rset.getInt ( "LINEITEM_NO" ) );
                soShippingLocation.setCustomerPONo( rset.getString ( "CUSTOMER_PO_NO" ) );
                soShippingLocation.setCustomerName( rset.getString ( "CUSTOMER_NAME" ) );
                String partNumberQty = rset.getString ( "PART_NUMBER_QTY" );
                String[] partNumberQtys = partNumberQty.split( "#" );
                ArrayList partNumbers = new ArrayList ( Arrays.asList(partNumberQtys) );
                soShippingLocation.setPartNumberList( partNumbers );
                soShippingLocation.setRequestedDate( StringUtils.formatDateToString( rset.getDate ( "COOL_REQUESTED_DATE" ) ) );
                soShippingLocation.setScheduleDate( StringUtils.formatDateToString( rset.getDate ( "COOL_SCHEDULE_DATE" ) ) );
                String shippingLocationIdsStr = rset.getString ( "SHIPPING_LOCATION_IDS" );
                if ( shippingLocationIdsStr != null )
                {
                    String[] shippingLocationIdsArr = shippingLocationIdsStr.split( "," );
                    int[] shippingLocationIds = new int[shippingLocationIdsArr.length];
                    for ( int i=0; i < shippingLocationIdsArr.length; i++ )
                    {
                        int shipLocId = 0;
                        try{
                            shipLocId = Integer.parseInt( shippingLocationIdsArr[i] );
                        } catch ( NumberFormatException ignore ) {}
                        shippingLocationIds[i] = shipLocId;
                    }
                    soShippingLocation.setShippingLocationId( shippingLocationIds );
                }
                salesOrders.add ( soShippingLocation );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Sales Orders for Shipping Locations", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return salesOrders;
    }

    public static void saveSalesOrderShipLocatoins ( DBTransaction dbTransaction, ArrayList salesOrderList ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            int size = (salesOrderList != null) ? salesOrderList.size() : 0;
            pstmt1 = conn.prepareStatement( "DELETE FROM SALES_ORDER_SHIPPING_LOCATIONS_TABLE WHERE LINEITEM_ID = ?" );
            pstmt2 = conn.prepareStatement( "INSERT INTO SALES_ORDER_SHIPPING_LOCATIONS_TABLE( SALESORDER_ID, LINEITEM_ID, SHIPPING_LOCATION_ID ) VALUES ( ?, ?, ? )" );

            for ( int i=0; i < size; i++ )
            {
                SOShippingLocation soShippingLocation = ( SOShippingLocation ) salesOrderList.get(i);
                pstmt1.setInt ( 1, soShippingLocation.getLineItemId() );
                pstmt1.executeUpdate();

                int[] shippingLocationIds = soShippingLocation.getShippingLocationId();
                if ( shippingLocationIds != null )
                {
                    pstmt2.clearBatch();
                    for ( int j=0; j < shippingLocationIds.length; j++ )
                    {
                        pstmt2.setInt ( 1, soShippingLocation.getSalesOrderId() );
                        pstmt2.setInt ( 2, soShippingLocation.getLineItemId() );
                        pstmt2.setInt ( 3, shippingLocationIds[j] );
                        pstmt2.addBatch();
                    }
                    pstmt2.executeBatch();
                }
            }
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not save Sales Order Shipping Locations", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try
                {
                    conn.rollback();
                }catch (SQLException ignore ) { }
            }
            if ( pstmt1 != null )
            {
                try
                {
                    pstmt1.close();
                } catch (SQLException ignore ){}
            }
            if ( pstmt2 != null )
            {
                try
                {
                    pstmt2.close();
                } catch (SQLException ignore ){}
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                } catch (SQLException ignore ){}
            }
        }
    }
    public static LineItem getLineItem ( DBTransaction dbTransaction, int lineItemNumber, int salesOrderId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        LineItem lineItem = null;

        if((lineItemNumber == -1) || (salesOrderId == -1)) {
            return null;
        }

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT SO.SalesOrder_No, COOL.LineItem_ID, COOL.COOL_Line_Item_No, COOL.Product_ID, COOL.COOL_Qty, COOL.COOL_Unit_Cost, COOL.COOL_Amount, COOL.COOL_Requested_Date, COOL.COOL_Sugg_Schedule_Date_With_Substitutables, COOL.COOL_Sugg_Schedule_Date_Without_Substitutables, COOL.COOL_Schedule_Date, COOL.COOL_Revised_Date, COOL_Status, COOL.COOL_Taxable, P.Part_Number, P.P_Marketing_Part_Number, P.P_Comments FROM Customer_Open_Order_LineItem COOL, Sales_Order SO, PRODUCT P WHERE SO.salesOrder_id = COOL.salesOrder_id AND P.PRODUCT_ID = COOL.PRODUCT_ID AND cool_line_item_no = ? and SO.SalesOrder_ID = ?" );
            pstmt.setInt (1, lineItemNumber);
            pstmt.setInt (2, salesOrderId);

            rs = pstmt.executeQuery();

            if (rs.next())
            {
                lineItem = new LineItem();

                lineItem.setSalesOrderId (salesOrderId);
                lineItem.setSalesOrderNo (rs.getString ("SalesOrder_No"));
                lineItem.setLineItemId(rs.getInt("LineItem_ID"));
                lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));
                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("COOL_Qty"));
                lineItem.setUnitPrice(rs.getFloat("COOL_Unit_Cost"));
                lineItem.setAmount(rs.getFloat("COOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_Schedule_Date"),null));
                lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("COOL_Revised_Date"),null));
                lineItem.setStatus(rs.getString("COOL_Status"));
                lineItem.setIsTaxable((rs.getInt("COOL_Taxable") == 0));
                String suggDate = lineItem.getSuggScheduleDateWithoutSubstitutables();
                lineItem.setSuggScheduleDate (suggDate);
                lineItem.setManufacturingPartNo(rs.getString("Part_Number"));
                lineItem.setMarketingPartNo(rs.getString("P_Marketing_Part_Number"));
                lineItem.setDescription(rs.getString("P_Comments"));
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load line Item for lineItem Number: " + lineItemNumber + ", salesOrder ID: " + salesOrderId, sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItem;
   }
}

